﻿CREATE PROCEDURE [ServiceEngine].[DeleteSchema]
@ID UNIQUEIDENTIFIER
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SET NOCOUNT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		-- Delete Imports
		DELETE ServiceEngine.SchemaImports
		WHERE SchemaID = @ID
		
		-- Delete Parent/Child Dependencies
		DELETE ServiceEngine.SchemaObjectDependencies
		FROM ServiceEngine.SchemaObjectDependencies JOIN
			ServiceEngine.SchemaObjects ON 
			ServiceEngine.SchemaObjectDependencies.SchemaObjectID = ServiceEngine.SchemaObjects.SchemaObjectID
		WHERE
			ServiceEngine.SchemaObjects.SchemaID = @ID

		DELETE ServiceEngine.SchemaObjectDependencies
		FROM ServiceEngine.SchemaObjectDependencies JOIN
			ServiceEngine.SchemaObjects ON 
			ServiceEngine.SchemaObjectDependencies.ParentObjectID = ServiceEngine.SchemaObjects.SchemaObjectID
		WHERE
			ServiceEngine.SchemaObjects.SchemaID = @ID

		-- Delete DataEntities
		DELETE ServiceEngine.DataEntities
		FROM ServiceEngine.DataEntities JOIN
			ServiceEngine.SchemaObjects ON 
			ServiceEngine.DataEntities.DataEntityID = ServiceEngine.SchemaObjects.DataEntityID
		WHERE
			ServiceEngine.SchemaObjects.SchemaID = @ID

		DELETE ServiceEngine.[Names]
		FROM ServiceEngine.[Names] JOIN
			ServiceEngine.SchemaObjects ON 
			ServiceEngine.[Names].EntityID = ServiceEngine.SchemaObjects.DataEntityID
		WHERE
			ServiceEngine.SchemaObjects.SchemaID = @ID

		-- Delete SchemaObjects
		DELETE ServiceEngine.SchemaObjects
		WHERE SchemaID = @ID

		-- Delete Schemas
		DELETE	ServiceEngine.[Schemas] 
		WHERE	SchemaID = @ID

		DELETE	ServiceEngine.Names
		WHERE	EntityID = @ID and EntityTypeID = 9

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

		RAISERROR (@ErrorMessage, 
				   @ErrorSeverity,
				   1);
	END CATCH
END

